﻿
CREATE procedure [dbo].[FC_Forescast_Search_PorPeriodo]  
@IdProduct INT, 
@IdCustomer INT,
@IdSegmentPL INT,
@Activos INT
as
BEGIN
IF @Activos = 1
	BEGIN
		SELECT isnull(AA.IdForecast,0)as IdForecast, AA.IdCustomer, @IdSegmentPL AS IdSegmentPL, coalesce(AA.IdProduct,@IdProduct) as IdProduct, AA.Codigo, AA.IdPeriod, AA.IdEntityF, 
		substring(convert(varchar(7),AA.Period),1,4)+'-'+substring(convert(varchar(7),AA.Period),5,2) as Period, ISNULL(AA.Volume,0) AS Volume, isnull(AA.DesProduct,'') as DesProduct,
		(ISNULL(AA.Volume,0)* isnull(AA.ValuePC,0)) as ValuePC, isnull(AA.CurrencyPC,'') as CurrencyPC, (ISNULL(AA.Volume,0) * isnull(AA.ValuesSP,0)) as ValuesSP, isnull(AA.CurrencySP,'') as CurrencySP 
		FROM
			(SELECT F.IdForecast, C.IdCustomer, SE.IdSegmentPL, PR.IdProduct, C.Codigo, P.IdPeriod, P.IdEntityF, P.Period, P.DateClose, ISNULL(F.Volume,'') AS Volume, PR.DesProduct,
				CASE WHEN P.DateClose < DATEADD(MONTH, 2, GETDATE()) THEN 0 ELSE 1 END ACTIVE,
				PC.Value as ValuePC, PC.Currency as CurrencyPC, SP.Value as ValuesSP, SP.Currency as CurrencySP
			FROM FC_Period P 
				 LEFT JOIN FC_Customer C ON P.IdEntityF = C.IdEntityF  
				 LEFT JOIN FC_Forecast F ON F.IdPeriod = P.IdPeriod AND F.IdCustomer = C.IdCustomer and F.IdProduct = @IdProduct
				 LEFT JOIN FC_Product PR ON F.IdProduct = PR.IdProduct
				 LEFT JOIN FC_SegmentPL SE ON SE.IdSegmentPL = PR.IdSegmentPL	
				 LEFT JOIN FC_PriceCost PC ON PR.IdEntityF = PC.IdEntityF AND PR.IdProduct = PC.IdProduct
				-- LEFT JOIN FC_SalePrice SP ON F.IdCustomer = SP.IdCustomer AND F.IdProduct = SP.IdProduct
				LEFT JOIN( 
							SELECT top 1 D.IdCustomer, C.IdProduct, A.Price as Value, A.CuryID as Currency, B.IdEntityF, YEAR(A.Date_Snapshot) as Agno
							FROM PL_Price_Countries_History A
							LEFT JOIN FC_Entity B ON A.Country = B.DesEntity --AND B.IdEntityF = B.IdEntityF
							LEFT JOIN FC_PRODUCT C ON A.InvtID = C.CodProduct AND B.IdEntityF = C.IdEntityF
							LEFT JOIN FC_Customer D ON A.CustomerId = D.Codigo AND D.IdEntityF = C.IdEntityF
							WHERE A.CustomerId <>'' AND  D.Codigo IS NOT NULL AND D.IdCustomer = @IdCustomer and IdProduct = @IdProduct
							ORDER BY 1,2,5 desc
						 ) SP ON F.IdCustomer = SP.IdCustomer AND F.IdProduct = SP.IdProduct
			WHERE 
				
				((@Activos = 1) or ((P.DateClose > DATEADD(MONTH, -10, GETDATE()) AND P.DateClose < DATEADD(MONTH, 2, GETDATE())))) and
				((@Activos = 0) or ((P.DateClose > DATEADD(MONTH, 2, GETDATE()) AND P.DateClose < DATEADD(MONTH, 14, GETDATE()))))  
				 
				) AA 
		WHERE AA.IdCustomer IN (select IdCustomer from FC_Forecast where IdProduct = @IdProduct AND IdCustomer = @IdCustomer)	
			
		order by AA.Codigo	
	END
IF @Activos = 0
	BEGIN
		DECLARE @CodigoProducto VARCHAR(50)
		
		select @CodigoProducto = A.CodProduct from FC_Product A where  a.IdProduct = @IdProduct
	
		SELECT isnull(AA.IdForecast,0)as IdForecast, AA.IdCustomer, @IdSegmentPL AS IdSegmentPL, coalesce(AA.IdProduct,@IdProduct) as IdProduct, AA.Codigo, AA.IdPeriod, AA.IdEntityF, 
		substring(convert(varchar(7),AA.Period),1,4)+'-'+substring(convert(varchar(7),AA.Period),5,2) as Period, sum(ISNULL(BB.Cantidad_eq,0)) AS Volume, isnull(AA.DesProduct,'') as DesProduct,
		sum(isnull(BB.Costo,0)) as ValuePC, isnull(AA.CurrencyPC,'') as CurrencyPC, sum(isnull(BB.Monto,0)) as ValuesSP, isnull(AA.CurrencySP,'') as CurrencySP 
		FROM
			(SELECT F.IdForecast, C.IdCustomer, SE.IdSegmentPL, PR.IdProduct, C.Codigo, P.IdPeriod, P.IdEntityF, P.Period, P.DateClose, ISNULL(F.Volume,'') AS Volume, PR.DesProduct,
				CASE WHEN P.DateClose < DATEADD(MONTH, 2, GETDATE()) THEN 0 ELSE 1 END ACTIVE,
				PC.Value as ValuePC, PC.Currency as CurrencyPC, SP.Value as ValuesSP, SP.Currency as CurrencySP
			FROM FC_Period P 
				 LEFT JOIN FC_Customer C ON P.IdEntityF = C.IdEntityF  
				 LEFT JOIN FC_Forecast F ON F.IdPeriod = P.IdPeriod AND F.IdCustomer = C.IdCustomer and F.IdProduct = @IdProduct
				 LEFT JOIN FC_Product PR ON F.IdProduct = PR.IdProduct
				 LEFT JOIN FC_SegmentPL SE ON SE.IdSegmentPL = PR.IdSegmentPL	
				 LEFT JOIN FC_PriceCost PC ON PR.IdEntityF = PC.IdEntityF AND PR.IdProduct = PC.IdProduct
				 LEFT JOIN FC_SalePrice SP ON F.IdCustomer = SP.IdCustomer AND F.IdProduct = SP.IdProduct
			WHERE 
				
				((@Activos = 1) or ((P.DateClose > DATEADD(MONTH, -10, GETDATE()) AND P.DateClose < DATEADD(MONTH, 2, GETDATE())))) and
				((@Activos = 0) or ((P.DateClose > DATEADD(MONTH, 2, GETDATE()) AND P.DateClose < DATEADD(MONTH, 14, GETDATE()))))  
				 -- AND C.IdEntityF = 2
				) AA 
				
				LEFT JOIN 
				(
					SELECT MO.Budget, (SUM(DVL.Costo) * MO.Budget) as Costo , (SUM(DVL.Monto)* MO.Budget) as Monto, SUM(DVL.Cantidad_eq) as Cantidad_eq, DVL.IdCliente, DVL.CODIGOPRODUCTO, DVL.Agno, DVL.Mes 
					FROM DetalleVentas_LatamFinal DVL 
							LEFT JOIN dbo.MONEDA MO ON  DVL.Pais = MO.Pais AND MO.Agno = YEAR(DVL.FechaDocumento)
					group by MO.Budget,DVL.IdCliente, DVL.CODIGOPRODUCTO, DVL.Agno, DVL.Mes
				) BB ON BB.IdCliente = AA.Codigo AND BB.CODIGOPRODUCTO = @CodigoProducto   
  				AND BB.Agno = convert(int,SUBSTRING(convert(varchar(6),AA.Period),1,4)) 
				AND BB.Mes = convert(int,SUBSTRING(convert(varchar(6),AA.Period),5,2))
				
		WHERE AA.IdCustomer IN (select IdCustomer from FC_Forecast where IdProduct = @IdProduct AND IdCustomer = @IdCustomer)	
				-- and AA.IdProduct = @IdProduct
		GROUP BY AA.IdForecast, AA.IdCustomer, AA.IdProduct, AA.Codigo, AA.IdPeriod, AA.IdEntityF, 
				 AA.Period, AA.DesProduct, AA.CurrencyPC, AA.CurrencySP
		order by AA.Codigo		
	END
	
	
END